package org.kunteng.yunac.util;

import org.json.JSONArray;
import org.json.JSONException;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

/**
 * 从csv文件中手动导入渠道商信息和路由信息
 * 
 * @author luzhirui
 *
 */
public class ExcelToDB {
  private static String fileDir = System.getProperty("user.dir") + "\\file\\";
//  private static String fileDir = "/opt/shell/";
  private static BufferedReader reader;
  private static HashMap<String, String> codeMap = new HashMap<String, String>();

  public static void main(String[] args) {
    start();
  }
  
  /**
   * 程序入口
   */
  public static void start() {
    try {
      int flag1 = 0,flag2 = 0,flag3 = 0,flag4 = 0,flag5 = 0,flag6 = 0,flag7 = 0,flag8 = 0,flag9 = 0,flag10 = 0,flag11=0,flag12=0,flag13=0,flag14 = 0,flag15=0,flag16=0,flag17=0,flag18=0;
      
      System.out.println("执行开始：" + new Date());
      System.out.println("1、清空临时表imp_tp_mac开始");
      // 清空临时表imp_tp_mac
      String sql = "TRUNCATE TABLE imp_tp_mac";
      flag1 = executeSql(sql);
      System.out.println("1、清空临时表imp_tp_mac结束，执行结果flag1="+flag1);
      
      if (flag1 == 1) {
        System.out.println("2、读取路由信息文件保存数据到临时表开始");
        // 读取路由信息文件保存数据到临时表
        String mac_file = fileDir + "mac.csv";
        ArrayList<String[]> mac_list = readCsv(mac_file, "UTF-8", ",");
        flag2 = saveMac(mac_list);
        System.out.println("2、读取路由信息文件保存数据到临时表结束，执行结果flag2="+flag2);
      }
      
      if (flag2 == 1) {
        System.out.println("3、清空临时表imp_tp_company开始");
        // 清空临时表imp_tp_company
        sql = "TRUNCATE TABLE imp_tp_company";
        flag3 = executeSql(sql);
        System.out.println("3、清空临时表imp_tp_company结束，执行结果flag3="+flag3);
      }
      
      if (flag3 == 1) {
        System.out.println("4、读取渠道商信息文件保存数据到临时表开始");
        // 读取渠道商信息文件保存数据到临时表
        // String company_file = fileDir + "company.csv";
        // ArrayList<String[]> company_list = readCsv(company_file, "UTF-8", ",");
        String company_file = fileDir + "company.xls";
        ArrayList<String[]> company_list = readExcel(company_file);
        flag4 = saveCompany(company_list);
        System.out.println("4、读取渠道商信息文件保存数据到临时表结束，执行结果flag4="+flag4);
      }
      
      if (flag4 == 1) {
        System.out.println("5、调用存储过程获取渠道商层级信息，插入到临时表imp_tp_level中开始");
        // 调用存储过程获取渠道商层级信息，插入到临时表imp_tp_level中
        sql = "CALL getChannelLevel(0)";
        flag5 = executeSql(sql);
        System.out.println("5、调用存储过程获取渠道商层级信息，插入到临时表imp_tp_level中结束，执行结果flag5="+flag5);
      }
      
      if (flag5 == 1) {
        System.out.println("6、渠道商信息入库开始");
        // 渠道商信息入库
        flag6 = saveChannel();
        System.out.println("6、渠道商信息入库结束，执行结果flag6="+flag6);
      }
      
      if (flag6 == 1) {
        System.out.println("7、更新一级渠道商的父渠道商编号开始");
        // 更新一级渠道商的父渠道商编号
        sql = "UPDATE channel c,imp_tp_company t SET c.pid='1' WHERE c.id=t.channelId AND t.parent_id=0";
        flag7 = executeSql(sql);
        System.out.println("7、更新一级渠道商的父渠道商编号结束，执行结果flag7="+flag7);
      }
     
      if (flag7 == 1) {
        System.out.println("8、更新非一级渠道商的父渠道商编号开始");
        // 更新非一级渠道商的父渠道商编号
        sql = "UPDATE channel c,imp_tp_company t1,imp_tp_company t2 SET c.pid=t2.channelId WHERE c.id=t1.channelId AND t1.parent_id=t2.id";
        flag8 = executeSql(sql);
        System.out.println("8、更新非一级渠道商的父渠道商编号结束，执行结果flag8="+flag8);
      }
      
      if (flag8 == 1) {
        System.out.println("9、路由信息入库开始");
        // 路由信息入库
        sql = "INSERT INTO router (mac,ssid,STATUS,c_wgt)" 
              + " SELECT tm.mac,CONCAT('K.T.WiFi_',RIGHT(tm.mac,6)),-1,CASE WHEN tc.type='1' THEN 1000 ELSE tl.nLevel+1 END"
              + " FROM (SELECT mac,MAX(company_id) AS company_id FROM imp_tp_mac  WHERE mac<>'NULL' GROUP BY mac) tm,"
              + " imp_tp_company tc,imp_tp_level tl WHERE tm.company_id=tc.id AND tc.id=tl.id"
              + " AND NOT EXISTS(SELECT * FROM router r WHERE r.mac=tm.mac)";
        flag9 = executeSql(sql);
        System.out.println("9、路由信息入库结束，执行结果flag9="+flag9);
      }

      if (flag9 == 1) {
        System.out.println("10、路由分布信息入库开始");
        // 路由分布信息入库
        sql = "INSERT INTO router_distribute(mac,cid)"
              +" SELECT tm.mac,tc.channelId"
              +" FROM router r,imp_tp_mac tm,imp_tp_company tc"
              +" WHERE r.mac=tm.mac AND tm.company_id=tc.id AND tm.mac<>'NULL' AND tc.channelId IS NOT NULL"
              +" AND NOT EXISTS(SELECT * FROM router_distribute rd WHERE rd.cid=tc.channelId AND rd.mac=tm.mac)";
        flag10 = executeSql(sql);
        System.out.println("10、路由分布信息入库结束，执行结果flag10="+flag10);
      }
      
      if (flag10 == 1) {
        System.out.println("11、清空临时表imp_tp_distribute开始");
        // 清空临时表imp_tp_distribute
        sql = "TRUNCATE TABLE imp_tp_distribute";
        flag11 = executeSql(sql);
        System.out.println("11、清空临时表imp_tp_distribute结束，执行结果flag11="+flag11);
      }
      
      if (flag11 == 1) {
        System.out.println("12、临时路由分配信息入库开始");
        // 临时路由分配信息入库
        sql = "INSERT INTO imp_tp_distribute"
              +" SELECT tm.mac,tc.channelId,tc.id FROM router r,imp_tp_mac tm,imp_tp_company tc"
              +" WHERE r.mac=tm.mac AND tm.company_id=tc.id AND tm.mac<>'NULL' AND tc.channelId IS NOT NULL";
        flag12 = executeSql(sql);
        System.out.println("12、临时路由分配信息入库结束，执行结果flag12="+flag12);
      }
      
      if (flag12 == 1) {
        System.out.println("13、路由分配信息补录开始");
        // 渠道商信息入库
        flag13 = routerDistributePatch();
        System.out.println("13、路由分配信息补录结束，执行结果flag13="+flag13);
      }
      
      if (flag13 == 1) {
        System.out.println("14、清空临时表imp_tp_whitelist开始");
        // 清空临时表imp_tp_whitelist
        sql = "TRUNCATE TABLE imp_tp_whitelist";
        flag14 = executeSql(sql);
        System.out.println("14、清空临时表imp_tp_whitelist结束，执行结果flag14="+flag14);
      }
      
      if (flag14 == 1) {
        System.out.println("15、读取白名单信息文件保存数据到临时表开始");
        // 读取渠道商信息文件保存数据到临时表
        String white_list_file = fileDir + "white_list.csv";
        ArrayList<String[]> white_list = readCsv(white_list_file, "UTF-8", "\t");
        flag15 = saveWhiteList(white_list);
        System.out.println("15、读取白名单信息文件保存数据到临时表结束，执行结果flag15="+flag15);
      }
      
      if (flag15 == 1) {
        System.out.println("16、白名单信息入库开始");
        // 路由分布信息入库
        sql = "INSERT INTO visitor_auth(visitor_mac,type,cid)"
              +" SELECT tw.mac,1,tc.channelId FROM imp_tp_whitelist tw,imp_tp_company tc WHERE tw.company_id=tc.id AND tc.channelId IS NOT NULL"
              +" AND NOT EXISTS (SELECT * FROM visitor_auth v WHERE v.cid=tc.channelId AND v.visitor_mac=tw.mac)";
        flag16 = executeSql(sql);
        System.out.println("16、白名单信息入库结束，执行结果flag16="+flag16);
      }
      
      if (flag16 == 1) {
        System.out.println("17、给渠道商赋予权限开始");
        // 给渠道商赋予权限
        sql = "INSERT INTO channel_has_sysauth(cid,auth_id) "
              +"SELECT DISTINCT tc.channelId,sa.id FROM imp_tp_company tc,sys_auth sa "
              +"WHERE tc.type='0' AND (sa.default_flag='1' OR sa.channel_permission_flag='1') "
              +"AND NOT EXISTS(SELECT * FROM channel_has_sysauth WHERE cid=tc.channelId AND auth_id=sa.id)";
        flag17 = executeSql(sql);
        System.out.println("17、给渠道商赋予权限结束，执行结果flag17="+flag17);
      }
      
      if (flag17 == 1) {
        System.out.println("18、给店铺赋予权限开始");
        // 给店铺赋予权限
        sql = "INSERT INTO channel_has_sysauth(cid,auth_id) "
              +"SELECT DISTINCT tc.channelId,sa.id FROM imp_tp_company tc,sys_auth sa "
              +"WHERE tc.type='1' AND (sa.default_flag='1' OR (sa.merchant_permission_flag='1' AND sa.id<>5200)) "
              +"AND NOT EXISTS(SELECT * FROM channel_has_sysauth WHERE cid=tc.channelId AND auth_id=sa.id)";
        flag18 = executeSql(sql);
        System.out.println("18、给店铺赋予权限结束，执行结果flag18="+flag18);
      }
      
      System.out.println("执行结束：" + new Date());

    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * 读取csv文件
   * 
   * @param filename
   * @param encode
   *  * @param split
   * @return 文件记录列表，每一行用数组存放
   */
  public static ArrayList<String[]> readCsv(String filename, String encode, String split ) {
    ArrayList<String[]> list = new ArrayList<String[]>();
    try {
      FileInputStream fis = new FileInputStream(filename);
      reader = new BufferedReader(new InputStreamReader(fis, encode));
      reader.readLine();
      String line = null;
      while ((line = reader.readLine()) != null) {
        String item[] = line.split(split);
        list.add(item);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }

  /**
   * 将csv文件中路由信息导入到临时表imp_tp_mac中
   * 
   * @param mac_list
   * @return
   */
  public static int saveMac(ArrayList<String[]> mac_list) {
    int flag = 0;
    Connection conn = new DBConn().getConn();
    try {
      conn.setAutoCommit(false);
      Statement st = conn.createStatement();
      for (String[] m_array : mac_list) {
        // 路由信息入库临时表
        String sql = "INSERT INTO imp_tp_mac(company_id, mac) VALUES(" + m_array[0] + ",'" + m_array[1] + "')";
        st.addBatch(sql);
      }
      st.executeBatch();
      conn.commit();
      st.close();
      conn.close();
      
      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
      try {
        conn.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
    }

    return flag;
  }

  /**
   * 将csv文件中渠道信息导入到临时表imp_tp_company中
   * 
   * @param company_list
   * @return
   */
  public static int saveCompany(ArrayList<String[]> company_list) {
    int flag = 0;
    Connection conn = new DBConn().getConn();
    try {
      conn.setAutoCommit(false);
      Statement st = conn.createStatement();
      for (String[] c_array : company_list) {
        // System.out.println("读取company表格数据："+c_array[0]+" "+c_array[1]+" "+c_array[2]+" "+c_array[3]+" "+c_array[4]+" "+c_array[5]+" "+c_array[6]+" "+c_array[8]);
        /* 手机号码科学计数法特殊处理 */
        String tel = c_array[3];
        try {
          BigDecimal db = new BigDecimal(tel);
          tel = db.toPlainString();
        } catch (Exception e) {
        }
        // 渠道商信息入库临时表
        String sql = "INSERT INTO imp_tp_company(id,parent_id,company_name,tel,address,user_name,user_email,type)"
                         + "VALUES (" + c_array[0] + "," + c_array[1] + ",'" + c_array[2] + "','" + tel + "','" + c_array[4] + "','" + c_array[5] + "','" + c_array[6] + "','" + c_array[8] + "')";
        st.addBatch(sql);
      }
      st.executeBatch();
      conn.commit();
      st.close();
      conn.close();
      
      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
      try {
        conn.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
    }

    return flag;
  }
  
  /**
   * 将csv文件中白名单信息导入到临时表imp_tp_whitelist中
   * 
   * @param white_list
   * @return
   */
  public static int saveWhiteList(ArrayList<String[]> white_list) {
    int flag = 0;
    Connection conn = new DBConn().getConn();
    try {
      conn.setAutoCommit(false);
      Statement st = conn.createStatement();
      for (String [] w_array : white_list) {
        // System.out.println("=>" + w_array[0] +" " + w_array[1]);
        try {
          JSONArray jsonArray = new JSONArray(w_array[1]);
          for (int i = 0; i < jsonArray.length(); i++) {
            String str = jsonArray.get(i).toString();
            StringBuffer mac = new StringBuffer();
            for (int j = 0; j < str.length(); j++) {
              mac.append(Character.toLowerCase(str.charAt(j)));
              if (j % 2 != 0 && j != str.length() - 1) {
                mac.append(":");
              }
            }
            // 白名单信息入库临时表(过滤掉重复数据)
            String sql = "REPLACE INTO imp_tp_whitelist(company_id, mac) VALUES(" + w_array[0] + ",'" + mac + "')";
            st.addBatch(sql);
          }
        } catch (JSONException e) {
          e.printStackTrace();
        }
        
      }
      
      st.executeBatch();
      conn.commit();
      st.close();
      conn.close();
      
      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
      try {
        conn.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
    }

    return flag;
  }  

  /**
   * 渠道商信息入库
   * @return
   */
  public static int saveChannel() {
    int flag = 0;
    try {
      Connection conn = new DBConn().getConn();
      Connection conn1 = new DBConn().getConn();
      Connection conn2 = new DBConn().getConn();

      Statement st = conn.createStatement();
      Statement st1 = conn1.createStatement();
      Statement st2 = conn2.createStatement();

      /* 从临时表中遍历所有的渠道商信息，解析处理之后正式入库保存 */
      String sql = "select c.*,l.nLevel+1 as level from imp_tp_company c,imp_tp_level l where c.id=l.id ORDER BY l.nLevel ASC,c.id ASC";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        // System.out.println(re.getString("id") + "=>" + re.getString("user_email") + "=>" + re.getString("company_name") + "->" + re.getString("level"));
        String account = re.getString("user_email");
        String id = re.getString("id");
        String weight = re.getString("level");
        String name = re.getString("company_name");
        String tel = re.getString("tel");
        String addr = re.getString("address");
        String contact = re.getString("user_name");
        String type = re.getString("type");

        // 如果数据库中没有该渠道商账号
        if (!"".equals(account) && !"null".equals(account) && !"NULL".equals(account) && !checkChannel(account)) {
          /* 生成渠道商编号 */
          String channelId = StringUtil.random();
          // 编号在数据库中已使用则重新生成，直到没有使用
          while (checkChannelId(channelId)) {
            channelId = StringUtil.random();
          };
          codeMap.put(id, channelId);

          // 根据邮箱加密生成密码
          String passwd = Encodes.entryptPassword(account);
          
          // type为1的是店铺，权值为1000
          if ("1".equals(type)) {
            weight = "1000";
          }

          // 渠道商信息入库
          sql = "INSERT INTO channel(id,account,passwd,login_flag,create_time,weight,type,del_flag)"
                + "VALUES ('" + channelId + "','" + account + "','" + passwd + "','1',now()," + weight + "," + type + ",'0')";
          st2.execute(sql);

          // 渠道商扩展信息入库
          sql = "INSERT INTO c_info(cid,name,tel,addr,contact,province,city,area,category)" 
                + "VALUES ('" + channelId + "','" + name + "','" + tel + "','" + addr + "','" + contact + "','北京市','市辖区','海淀区',1)";
          st2.execute(sql);
          
          /* 拼接渠道商层级编号 */
          String levelCode = "";
          String levelId = "";
          sql = "SELECT getParentList(" + id + ") as levelId";
          ResultSet re1 = st1.executeQuery(sql);
          while (re1.next()) {
            levelId = re1.getString("levelId");
          }
          String[] id_array = levelId.split(",");
          for (int i = 0; i < id_array.length; i++) {
            if (!"0".equals(id_array[i])) {
              String rid = codeMap.get(id_array[i]);
              levelCode += rid;
              if (i != id_array.length - 1) {
                levelCode += "#";
              }
            }
          }
          levelCode = "1#" + levelCode;
          
          // 更新渠道商层级编号
          sql = "UPDATE channel SET code='" + levelCode + "' WHERE id='" + channelId + "'";
          st2.execute(sql);
          
          /*
          // 给账号赋予默认操作权限
          if ("0".equals(type)) {
            sql = "INSERT INTO channel_has_sysauth(cid,auth_id) ";
            sql += "SELECT DISTINCT '"+channelId+"',id FROM sys_auth sa WHERE sa.default_flag='1' OR sa.channel_permission_flag='1' ";
            sql += "AND NOT EXISTS(SELECT * FROM channel_has_sysauth WHERE cid='"+channelId+"' AND auth_id=sa.id)";
            st2.execute(sql);
          } else {
            sql = "INSERT INTO channel_has_sysauth(cid,auth_id) ";
            sql += "SELECT DISTINCT '"+channelId+"',id FROM sys_auth sa WHERE sa.default_flag='1' OR (sa.merchant_permission_flag='1' AND sa.id<>5200) ";
            sql += "AND NOT EXISTS(SELECT * FROM channel_has_sysauth WHERE cid='"+channelId+"' AND auth_id=sa.id)";
            st2.execute(sql);
          }
          */

          // 更新临时表渠道商编号
          sql = "UPDATE imp_tp_company  SET channelId='" + channelId + "' WHERE id=" + id;
          st2.execute(sql);
        }
      }

      st.close();
      st1.close();
      st2.close();
      conn.close();
      conn1.close();
      conn2.close();

      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
    }
    return flag;
  }

  /**
   * 判断渠道商账号在数据库中是否已存在
   * @param account
   * @return
   */
  public static boolean checkChannel(String account) {
    boolean flag = true;
    String channelId = "";
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      ResultSet re = st.executeQuery("select c.id from channel c where c.account='" + account + "'");
      while (re.next()) {
        channelId = re.getString("id");
      }

      // 数据库没有该账号
      if ("".equals(channelId)) {
        flag = false;
      } else {
        // 重复账号同步渠道商编号到临时表
        String sql = "UPDATE imp_tp_company tc SET tc.channelId='"+channelId+"',tc.exists_flag='1' WHERE tc.user_email='" + account + "'";
        executeSql(sql);
        System.out.println("重复账号：" + account);
        
        Connection conn1 = new DBConn().getConn();
        Statement st1 = conn1.createStatement();
        ResultSet re1 = st1.executeQuery("select id from imp_tp_company tc where tc.user_email='" + account + "'");
        while (re1.next()) {
          int id = Integer.parseInt(re1.getString("id"));
          codeMap.put(String.valueOf(id), channelId);
        }
        st1.close();
        conn1.close();
      }
      st.close();
      conn.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return flag;
  }

  /**
   * 判断随机生成的渠道商编号在数据库是否已使用
   * @param code
   * @return
   */
  public static boolean checkChannelId(String code) {
    boolean flag = true;
    int count = 0;
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      ResultSet re = st.executeQuery("select count(0) as count from channel where id='" + code + "'");
      while (re.next()) {
        count = Integer.parseInt(re.getString("count"));
      }
      conn.close();

      // 数据库没有使用该编号
      if (count == 0) {
        flag = false;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return flag;
  }
  
  /**
   * 路由分配信息补录
   */
  public static int routerDistributePatch() {
    int flag = 0;
    Connection conn = new DBConn().getConn();
    try {
      conn.setAutoCommit(false);
      Statement st = conn.createStatement();
      String sql = "SELECT td.mac,c.* FROM channel c,imp_tp_company tc,";
      sql += " (SELECT mac,MAX(company_id) cid FROM imp_tp_distribute GROUP BY mac) td";
      sql += " WHERE tc.channelId=c.id AND tc.id=td.cid";
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String mac = re.getString("mac");
        String code = re.getString("code");
        String arr [] = code.split("#");
        for (String cid : arr) {
          String insert_sql = "INSERT INTO router_distribute(mac,cid)";
          insert_sql += " SELECT '"+mac+"','"+cid+"' FROM DUAL";
          insert_sql += " WHERE NOT EXISTS(SELECT * FROM router_distribute rd WHERE rd.cid='"+cid+"' AND rd.mac='"+mac+"')";
          st.addBatch(insert_sql);
        }
      }
      st.executeBatch();
      conn.commit();
      st.close();
      conn.close();
      
      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
      try {
        conn.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
    }
    return flag;
  }
  
  
  /**
   * 公共执行sql方法
   * 
   * @return
   */
  public static int executeSql(String sql) {
    int flag = 0;
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      st.execute(sql);
      st.close();
      conn.close();
      
      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
    }
    return flag;
  }
  
  /**
   * 读取Excel文件
   * 
   * @param filename
   * @param encode
   *  * @param split
   * @return 文件记录列表，每一行用数组存放
   */
  public static ArrayList<String[]> readExcel(String filename) {
    ArrayList<String[]> list = new ArrayList<String[]>();
    try {
      File file = new File(filename);
      String[][] result = ExcelUtil.getData(file, 1);
      for (int i = 0; i < result.length; i++) {
        list.add(result[i]);
//        for (int j = 0; j < result[i].length; j++) {
//          System.out.print(result[i][j] + "\t\t");
//        }
//        System.out.println();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }

}